﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using MySql.Data.MySqlClient;
using CodeRobot.Utility;
using System.Windows.Forms;

namespace CodeRobot.DAL
{
    /// <summary>
    /// 生成导出Excel类
    /// </summary>
    public class ExcelHelper
    {

        /// <summary>
        /// 根据表名创建Controllers类
        /// </summary>
        /// <param name="strFilePath">保存路径</param>
        /// <param name="strProjectName">项目名</param>
        /// <param name="strTableName">表名</param>
        public static void CreateControllersClass(string strFilePath, string strProjectName, string strTableName, string strTableComment, string strDBName)
        {
            //读取版权信息
            CodeRobot.Utility.IniFile iniFile = new Utility.IniFile(Application.StartupPath + "\\config.ini");
            string strCompany = iniFile.GetString("COPYRIGHT", "COMPANY", "");
            string strAuthor = iniFile.GetString("COPYRIGHT", "AUTHOR", "");
            string strVersion = iniFile.GetString("COPYRIGHT", "VERSION", "");
            string strCode = iniFile.GetString("COPYRIGHT", "CODE", "");
            string strCreateDate = iniFile.GetString("BASE", "CREATE_DATE", "");

            string strTableNameUpper = CommonHelper.GetTableNameUpper(strTableName);

            string strPrimaryKey = CommonHelper.GetPrimaryKey(strTableName);//如：news_id

            string strAllColumnName = CommonHelper.GetAllColumnName(strTableName);
            string strAllColumnNameNotKey = CommonHelper.GetAllColumnNameNotKey(strTableName);

            string strClassName = CommonHelper.GetClassName(strTableName);//类名
            strClassName = CommonHelper.GetTableNameUpper(strClassName);
            string strTableNameSpec = CommonHelper.GetTableNameFirstLowerSecondUpper(strClassName);//如：newsTypes
            string strTableNameLower = strTableNameSpec.ToLower();//如：newstypes

            int mergeNum = CommonHelper.GetColumnNum(strTableName) + 1;//合并表头列数
            string strTableCommentNew = CommonHelper.GetColumnKeyComment(strTableComment);

            Directory.CreateDirectory(strFilePath);
            StreamWriter sw = new StreamWriter(strFilePath + "\\ExcelController.cs", false, Encoding.GetEncoding("utf-8"));
            sw.WriteLine("using System;");
            sw.WriteLine("using System.Linq;");
            sw.WriteLine("using System.Threading.Tasks;");
            sw.WriteLine("using Microsoft.AspNetCore.Mvc;");
            sw.WriteLine("using Microsoft.EntityFrameworkCore;");
            sw.WriteLine("using System.Collections.Generic;");
            sw.WriteLine("using " + strProjectName + ".Manage.Data;");
            sw.WriteLine("using " + strProjectName + ".Models;");
            sw.WriteLine("using OfficeOpenXml;");
            sw.WriteLine("using OfficeOpenXml.Style;");
            sw.WriteLine("using System.IO;");
            sw.WriteLine("using Aliyun.OSS;");
            sw.WriteLine("");
            sw.WriteLine("namespace " + strProjectName + ".Manage.Controllers");
            sw.WriteLine("{");
            sw.WriteLine("");
            sw.WriteLine("    /// <summary>");
            sw.WriteLine("    /// 版权所有: Copyright © " + DateTime.Now.Year.ToString() + " " + strCompany + ". 保留所有权利。");
            sw.WriteLine("    /// 内容摘要: " + strClassName + " 处理请求的类");
            sw.WriteLine("    /// 创建日期：" + Convert.ToDateTime(strCreateDate).ToString("yyyy年M月d日"));
            sw.WriteLine("    /// 更新日期：" + DateTime.Now.ToString("yyyy年M月d日"));
            sw.WriteLine("    /// 版    本：V" + strVersion + "." + strCode + " ");
            sw.WriteLine("    /// 作    者：" + strAuthor);
            sw.WriteLine("    /// </summary>");
            sw.WriteLine("    public class ExcelController : BaseController<ExcelController>");
            sw.WriteLine("    {");
            sw.WriteLine("");
            sw.WriteLine("        /// <summary>");
            sw.WriteLine("        /// " + strTableComment + " 实例化数据上下文");
            sw.WriteLine("        /// </summary>");
            sw.WriteLine("        /// <param name=\"context\"></param>");
            sw.WriteLine("        public ExcelController(" + strProjectName + "ManageContext context) : base(context)");
            sw.WriteLine("        {");
            sw.WriteLine("");
            sw.WriteLine("        }");
            sw.WriteLine("");
            sw.WriteLine("        /// <summary>");
            sw.WriteLine("        /// 导出" + strTableComment);
            sw.WriteLine("        /// </summary>");
            sw.WriteLine("        /// <param name=\"select_date\">筛选日期区间</param>");
            sw.WriteLine(GetSearchColumnComments(strDBName, strTableName));
            sw.WriteLine("        /// <returns></returns>");
            sw.WriteLine("        public IActionResult Export" + strClassName + "(string select_date" + GetSearchColumn(strDBName, strTableName) + ")");
            sw.WriteLine("        {");
            sw.WriteLine("            try");
            sw.WriteLine("            {");
            sw.WriteLine("                var save_dir = GetSettingInfoValue((int)ENUMHelper.SettingInfoType.UploadImgSavePath) ;");
            sw.WriteLine("                var temp_path = save_dir + \"\\\\\" + DateTime.Now.Year + \"\\\\\" + DateTime.Now.ToString(\"MMdd\") + \"\\\\\";");
            sw.WriteLine("                string file_name = DateTime.Now.Year + \"\\\\\" + DateTime.Now.ToString(\"MMdd\") + \"\\\\\" + CommonHelper.CreateNo() + \".xlsx\";");
            sw.WriteLine("                if (!Directory.Exists(temp_path))");
            sw.WriteLine("                {");
            sw.WriteLine("                    Directory.CreateDirectory(temp_path);");
            sw.WriteLine("                }");
            sw.WriteLine("                FileInfo file = new FileInfo(Path.Combine(save_dir!, file_name));");
            sw.WriteLine("                using (ExcelPackage package = new ExcelPackage(file))");
            sw.WriteLine("                {");
            sw.WriteLine("                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(\"导出数据\");");
            sw.WriteLine(CommonHelper.GetExportExcelItem(strDBName, strTableName));
            sw.WriteLine("");
            sw.WriteLine("                    // 设置表头");
            sw.WriteLine("                    worksheet.Cells[1, 1, 1, " + mergeNum + "].Merge = true;");
            sw.WriteLine("                    worksheet.Cells[1, 1].Value = \"" + strTableCommentNew + "\";");
            sw.WriteLine("                    // worksheet.Cells.Style.WrapText = true;");
            sw.WriteLine("                    worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;");
            sw.WriteLine("                    worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;");
            sw.WriteLine("                    worksheet.Row(1).Height = 30;");
            sw.WriteLine("                    worksheet.Row(2).Height = 25;");
            sw.WriteLine("                    // worksheet.Cells.Style.ShrinkToFit = true;// 单元格自动适应大小");
            sw.WriteLine("");
            sw.WriteLine("                    var result = from item in _context.Set<" + strClassName + ">()");
            sw.WriteLine("                             select new");
            sw.WriteLine("                             {");
            sw.WriteLine(CommonHelper.GetLeftSelectColumnName2(strProjectName, strDBName, strTableName, "item"));
            sw.WriteLine("                             };");
            sw.WriteLine("");
            sw.WriteLine(GetSearchWhere(strDBName, strTableName));
            if (CommonHelper.ChecktCreatedAtKey(strTableName, "created_at"))
            {
                sw.WriteLine("                if (!string.IsNullOrEmpty(select_date))");
                sw.WriteLine("                {");
                sw.WriteLine("                    select_date = select_date.Replace(\" - \", \",\");");
                sw.WriteLine("                    string[] splitDate = select_date.Split(new char[] { ',' });");
                sw.WriteLine("                    string start_at = splitDate[0];");
                sw.WriteLine("                    string end_at = splitDate[1];");
                sw.WriteLine("                    result = result.Where(t => t.created_at >= Convert.ToDateTime(start_at) && t.created_at <= Convert.ToDateTime(end_at).AddDays(1));");
                sw.WriteLine("                }");
                sw.WriteLine("");
                sw.WriteLine("                var list = result.OrderByDescending(t => t.created_at).ToList();");
            }
            else
            {
                sw.WriteLine("                var list = result.OrderByDescending(t => t." + strPrimaryKey + ").ToList();");
            }
            sw.WriteLine("                    if (list.Count() == 0)");
            sw.WriteLine("                    {");
            sw.WriteLine("                        return Json(new { code = 0, msg = \"暂无数据\", count = 0 });");
            sw.WriteLine("                    }");
            sw.WriteLine("                    // 填充值");
            sw.WriteLine("                    for (int i = 0; i < list.Count; i++)");
            sw.WriteLine("                    {");
            sw.WriteLine(CommonHelper.GetExportExcelItemValue(strDBName, strTableName));
            sw.WriteLine("                    }");
            // sw.WriteLine("");
            sw.WriteLine("                    package.Save();");
            sw.WriteLine("                }");
            sw.WriteLine("");
            sw.WriteLine("                // 上传至阿里云OSS");
            sw.WriteLine("                var new_file_name = \"" + strProjectName.ToLower() + "_" + strTableNameLower + "_\" + DateTime.Now.ToString(\"yyyyMMddHHmmss\") + CommonHelper.GenerateRandomNum(6);");
            sw.WriteLine("                var return_path = \"/\" + DateTime.Now.Year + \"/\" + DateTime.Now.ToString(\"MMdd\") + \"/\" + new_file_name + \".xlsx\";");
            sw.WriteLine("                using FileStream fi = new FileStream(Path.Combine(save_dir!, file_name), FileMode.Open);");
            // sw.WriteLine("                OssClient client = new OssClient(" + strProjectName + ".Utility.Config.AliyunOSS.Endpoint, " + strProjectName + ".Utility.Config.AliyunOSS.AccessKeyId, " + strProjectName + ".Utility.Config.AliyunOSS.AccessKeySecret);");
            sw.WriteLine("                OssClient client = new OssClient(GetSettingInfoValue((int)ENUMHelper.SettingInfoType.AliyunEndpoint), GetSettingInfoValue((int)ENUMHelper.SettingInfoType.AliyunAccessKeyId), GetSettingInfoValue((int)ENUMHelper.SettingInfoType.AliyunAccessKeySecret));");
            sw.WriteLine("                var key = \"upload/\" + DateTime.Now.Year + \"/\" + DateTime.Now.ToString(\"MMdd\") + \"/\";");
            sw.WriteLine("                using (MemoryStream memStream = new MemoryStream())");
            sw.WriteLine("                {");
            sw.WriteLine("                    client.PutObject(GetSettingInfoValue((int)ENUMHelper.SettingInfoType.AliyunBucketName), key, memStream);");
            sw.WriteLine("                }");
            sw.WriteLine("                ObjectMetadata metadata = new ObjectMetadata();");
            sw.WriteLine("                metadata.ContentType = \"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\";");
            sw.WriteLine("                client.PutObject(GetSettingInfoValue((int)ENUMHelper.SettingInfoType.AliyunBucketName), key + new_file_name + \".xlsx\", fi, metadata);");
            sw.WriteLine("");
            sw.WriteLine("                return Json(new { code = 0, msg = \"导出成功\", path = GetSettingInfoValue((int)ENUMHelper.SettingInfoType.AliyunCallbackServer) + \"/upload\" + return_path });");
            sw.WriteLine("            }");
            sw.WriteLine("            catch (Exception ex)");
            sw.WriteLine("            {");
            sw.WriteLine("                log.Fatal(ex, \" -> Export" + strClassName + "\");");
            sw.WriteLine("                return Json(new { code = 0, errcode = 40000, errmsg = ex.Message });");
            sw.WriteLine("            }");
            sw.WriteLine("        }");
            sw.WriteLine("");
            sw.WriteLine("    }");
            sw.WriteLine("}");
            sw.Close();

        }


        /// <summary>
        /// 获取更新字段
        /// </summary>
        /// <param name="strNewName"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public static string GetUpdateItems(string strDBName, string strNewName, string strTableName)
        {
            string strReturnValue = "";

            try
            {
                MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
                cn.Open();
                string strSql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT,EXTRA,COLUMN_DEFAULT,CHARACTER_SET_NAME FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA='" + strDBName + "' AND TABLE_NAME='" + strTableName + "' ORDER BY ordinal_position ";
                MySqlCommand cmd = new MySqlCommand(strSql, cn);
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string strColumnName = dr["COLUMN_NAME"].ToString();//字段列名
                    string strColumnKey = dr["COLUMN_KEY"].ToString();//是否是主键
                    string strColumnComment = dr["COLUMN_COMMENT"].ToString();//注释

                    string strValue = "                item." + strColumnName + " = " + strNewName + "." + strColumnName + ";\r\n";
                    strReturnValue += strValue;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取搜索字段列表", "GetSearchItemList", false);
            }

            return strReturnValue;

        }

        /// <summary>
        /// 获取搜索条件
        /// </summary>
        /// <param name="strDBName"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public static string GetSearchWhere(string strDBName, string strTableName)
        {
            string strReturnValue = "";

            try
            {
                MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
                cn.Open();
                string strSql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT,EXTRA,COLUMN_DEFAULT,CHARACTER_SET_NAME FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA='" + strDBName + "' AND TABLE_NAME='" + strTableName + "' ORDER BY ordinal_position ";
                MySqlCommand cmd = new MySqlCommand(strSql, cn);
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string strColumnName = dr["COLUMN_NAME"].ToString();//字段列名
                    string strColumnKey = dr["COLUMN_KEY"].ToString();//是否是主键
                    string strColumnComment = dr["COLUMN_COMMENT"].ToString();//注释
                    string strDataType = dr["DATA_TYPE"].ToString();//注释

                    if (strColumnComment.Contains("{搜索}"))
                    {
                        string strValue = "";
                        if (strDataType == "int" || strDataType == "bigint" || strDataType == "smallint")
                        {
                            strValue = "                if (" + strColumnName + " > 0)\r\n";
                            strValue += "                {\r\n";
                            strValue += "                   result = result.Where(t => t." + strColumnName + " == " + strColumnName + ");\r\n";
                            strValue += "                }\r\n";
                        }
                        else if (strDataType == "decimal")
                        {
                            strValue = "                if (" + strColumnName + " > 0)\r\n";
                            strValue += "                {\r\n";
                            strValue += "                   result = result.Where(t => t." + strColumnName + ".Contains(" + strColumnName + ".Trim()));\r\n";
                            strValue += "                }\r\n";
                        }
                        else if (strDataType == "bit")
                        {
                            strValue = "                if (" + strColumnName + " > -1)\r\n";
                            strValue += "                {\r\n";
                            strValue += "                    if (" + strColumnName + " == 1)\r\n";
                            strValue += "                    {\r\n";
                            strValue += "                      result = result.Where(t => t." + strColumnName + " == true);\r\n";
                            strValue += "                    }\r\n";
                            strValue += "                    if (" + strColumnName + " == 0)\r\n";
                            strValue += "                    {\r\n";
                            strValue += "                      result = result.Where(t => t." + strColumnName + " == false);\r\n";
                            strValue += "                    }\r\n";
                            strValue += "                }\r\n";
                        }
                        else
                        {
                            strValue = "                if (!string.IsNullOrEmpty(" + strColumnName + "))\r\n";
                            strValue += "                {\r\n";
                            strValue += "                   result = result.Where(t => t." + strColumnName + ".Contains(" + strColumnName + ".Trim()));\r\n";
                            strValue += "                }\r\n";
                        }

                        strReturnValue += strValue;
                    }
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取搜索条件", "GetSearchWhere", false);
            }

            return strReturnValue;

        }

        /// <summary>
        /// 获取搜索字段注释
        /// </summary>
        /// <param name="strDBName"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public static string GetSearchColumnComments(string strDBName, string strTableName)
        {
            string strReturnValue = "";

            try
            {
                MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
                cn.Open();
                string strSql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT,EXTRA,COLUMN_DEFAULT,CHARACTER_SET_NAME FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA='" + strDBName + "' AND TABLE_NAME='" + strTableName + "' ORDER BY ordinal_position ";
                MySqlCommand cmd = new MySqlCommand(strSql, cn);
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string strColumnName = dr["COLUMN_NAME"].ToString();//字段列名
                    string strColumnKey = dr["COLUMN_KEY"].ToString();//是否是主键
                    string strColumnComment = dr["COLUMN_COMMENT"].ToString();//注释
                    string strDataType = dr["DATA_TYPE"].ToString();//注释

                    if (strColumnComment.Contains("{搜索}"))
                    {
                        strColumnComment = CommonHelper.GetColumnKeyComment(strColumnComment);
                        string strValue = "        /// <param name=\"" + strColumnName + "\">" + strColumnComment + "</param>\r\n";
                        strReturnValue += strValue;
                    }
                }
                dr.Dispose();
                cn.Close();

                if (strReturnValue.Length > 0)
                {
                    strReturnValue = strReturnValue.Substring(0, strReturnValue.Length - 2);
                }
            }
            catch (Exception ex)
            {
                CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取搜索字段注释", "GetSearchColumnComments", false);
            }

            return strReturnValue;

        }

        /// <summary>
        /// 获取搜索字段
        /// </summary>
        /// <param name="strDBName"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public static string GetSearchColumn(string strDBName, string strTableName)
        {
            string strReturnValue = "";

            try
            {
                MySqlConnection cn = new MySqlConnection(CodeRobot.DBSqlHelper.DBMySQLHelper.ConnectionMySQL());
                cn.Open();
                string strSql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,COLUMN_KEY,COLUMN_COMMENT,EXTRA,COLUMN_DEFAULT,CHARACTER_SET_NAME FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA='" + strDBName + "' AND TABLE_NAME='" + strTableName + "' ORDER BY ordinal_position ";
                MySqlCommand cmd = new MySqlCommand(strSql, cn);
                MySqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    string strColumnName = dr["COLUMN_NAME"].ToString();//字段列名
                    string strColumnKey = dr["COLUMN_KEY"].ToString();//是否是主键
                    string strColumnComment = dr["COLUMN_COMMENT"].ToString();//注释
                    string strDataType = dr["DATA_TYPE"].ToString();//注释

                    if (strColumnComment.Contains("{搜索}"))
                    {
                        string strValue = "";
                        if (strDataType == "int" || strDataType == "bigint" || strDataType == "smallint")
                        {
                            strValue = ", int " + strColumnName + " = -1";
                        }
                        else if (strDataType == "decimal")
                        {
                            strValue = ", double " + strColumnName;
                        }
                        else if (strDataType == "bit")
                        {
                            strValue = ", int " + strColumnName + " = -1";
                        }
                        else
                        {
                            strValue = ", string " + strColumnName;
                        }

                        strReturnValue += strValue;
                    }
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                CodeRobot.Utility.LogHelper.Error(typeof(CodeHelper), ex, "获取搜索字段", "GetSearchColumn", false);
            }

            return strReturnValue;

        }

    }
}
